import mysql.connector
import re
import os
from mysql.connector import Error


class NovelProcessor:
    def __init__(self, db_config):
        self.db_config = db_config
        self.connection = None
        self.connect_to_db()

    def connect_to_db(self):
        """连接到MySQL数据库"""
        try:
            self.connection = mysql.connector.connect(**self.db_config)
            if self.connection.is_connected():
                print("成功连接到MySQL数据库")
        except Error as e:
            print(f"连接数据库时出错: {e}")

    def create_table(self):
        """创建存储小说章节的表"""
        create_table_query = """
        CREATE TABLE IF NOT EXISTS novel_chapters (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            content TEXT NOT NULL,
            chapter_order INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        """
        try:
            cursor = self.connection.cursor()
            cursor.execute(create_table_query)
            print("表创建成功或已存在")
        except Error as e:
            print(f"创建表时出错: {e}")

    def extract_chapters(self, file_path):
        """
        从TXT文件中提取章节
        假设章节格式为: 第X章 章节标题
        """
        chapters = []
        current_chapter = {"title": "", "content": ""}
        chapter_pattern = re.compile(r'.*章）')

        try:
            with open(file_path, 'r', encoding='gbk') as file:
                for line in file:
                    line = line.strip()
                    if chapter_pattern.search(line):
                        # 如果是新章节，保存上一章内容
                        if current_chapter["title"]:
                            chapters.append(current_chapter.copy())

                        # 开始新章节
                        current_chapter["title"] = line
                        current_chapter["content"] = ""
                    else:
                        # 添加内容到当前章节
                        if current_chapter["title"] and line:
                            current_chapter["content"] += line + "\n"

                # 添加最后一章
                if current_chapter["title"]:
                    chapters.append(current_chapter)

            print(f"从文件中提取了 {len(chapters)} 个章节")
            return chapters
        except Exception as e:
            print(f"读取文件时出错: {e}")
            return []

    def insert_chapters(self, chapters,bookId):
        """将章节插入数据库"""
        if not chapters:
            print("没有章节可插入")
            return

        insert_query = """
        INSERT INTO book_chapter_temp (title, content, px,bookId)
        VALUES (%s, %s, %s, %s)
        """

        try:
            cursor = self.connection.cursor()
            for i, chapter in enumerate(chapters):
                # 只插入非空内容
                if chapter["content"].strip():
                    cursor.execute(insert_query, (chapter["title"], chapter["content"], i + 1,bookId))

            self.connection.commit()
            print(f"成功插入 {cursor.rowcount} 个章节到数据库")
        except Error as e:
            print(f"插入数据时出错: {e}")
            self.connection.rollback()
        finally:
            if cursor:
                cursor.close()

    def process_novel(self, file_path,bookId):
        """处理小说的完整流程"""
        # 提取章节
        chapters = self.extract_chapters(file_path)

        # 插入数据库
        if chapters:
            self.insert_chapters(chapters,bookId)
        else:
            print("未提取到任何章节")

    def close_connection(self):
        """关闭数据库连接"""
        if self.connection and self.connection.is_connected():
            self.connection.close()
            print("数据库连接已关闭")


# 主程序
if __name__ == "__main__":
    # 数据库配置 - 请根据你的实际情况修改
    db_config = {
        'host': '43.143.252.56',
        'database': 'myblog',  # 请先创建这个数据库
        'user': 'root',
        'password': 'Dream2060#',
        'charset': 'utf8mb4'  # 支持存储中文
    }

    # 小说文件路径
    novel_file_path = 'E:\\123.txt'  # 替换为你的小说文件路径

    # 创建处理器实例
    processor = NovelProcessor(db_config)

    try:
        # 创建表
        # processor.create_table()

        # 处理小说
        if os.path.exists(novel_file_path):
            bookId = 10019
            processor.process_novel(novel_file_path,bookId)
        else:
            print(f"文件不存在: {novel_file_path}")
    finally:
        # 关闭连接
        processor.close_connection()